<?php
header("Content-type: text/html; charset=utf-8");

///////////////////////////////////////////////////////////////////////////////////////////////
/**
 * demo
 */
//$db = new ManageDB('127.0.0.1','root','123456','ymd');
//备份
//$result = $db->backup('','./a/b/c/');
//还原
//$result = $db->restore('./a/b/c/20181102185426_all_page_1.sql');
//print_r($result);
///////////////////////////////////////////////////////////////////////////////////////////////

defined('SYS_DB_PATH') or define('SYS_DB_PATH', './runtime/backup/');

/**
 * function ManageDB
 * Author   ZhuHaili
 * Create   2017-11-01
 * describe	数据备份与还原
 */
class ManageDB{
    public $db;
    public $database;
    public $sqldir;
    public $sqlEnd;
    public $msg;
    private $ds;

    /**
     * 初始化
     *
     * @param string $host
     * @param string $username
     * @param string $password
     * @param string $database
     * @param string $charset
     */
    function __construct($host = 'localhost', $username = 'root', $password = '123456', $database = 'test', $charset = 'utf8'){
        $this->ds = PHP_EOL;
        $this->sqlEnd = ";";
        $this->host = $host;
        $this->username = $username;
        $this->password = $password;
        $this->database = $database;
        $this->charset = $charset;
        set_time_limit(0); //无时间限制
        @ob_end_flush();
        //$this->_dbCon = @mysqli_connect($address, $account, $pwd, $dbname, $port);
        $this->db = @mysqli_connect ($this->host, $this->username, $this->password, $this->database) or die('<p class="dbDebug"><span class="err">Mysql Connect Error : </span>'.mysqli_error($this->db).'</p>');
        if(!$this->db){
            $errTxt = iconv("GB2312","UTF-8//IGNORE",mysqli_connect_error());
            exit('Error('.mysqli_connect_errno().'):'.$errTxt);
        }else{
            mysqli_query($this->db,'set names utf8');
        }
        //mysql_select_db ( $this->database, $this->db ) or die('<p class="dbDebug"><span class="err">Mysql Connect Error:</span>'.mysql_error().'</p>');
        //mysql_query('SET NAMES ' . $this->charset, $this->db);

    }

    /**
     * fun:	 新增查询数据库表
     * desc:
     */
    function getTables() {
        $res = mysqli_query($this->db,"SHOW TABLES");
        $tables = array();
        $i = 0;
        while( $row = mysqli_fetch_array($res,MYSQLI_NUM)) {
            $tables [$i] = $row[0];
            $i ++;
        }
        return $tables;
    }

    /**
     * fun:	 创建目录
     * desc:
     */
    private function pathcreates($path){
        $hint = 'Y';
        //数组去除空值
        $pathBy = array_filter(explode('/',$path));
        $pathNo = '';
        $pathAll = '';
        foreach($pathBy as $v){
            if($v == '.'||$v == '..'){
                $pathNo = $pathNo.$v.'/';
            }else{
                $pathAll = $pathAll.$pathNo.$v.'/';
                if(!file_exists($pathAll)){
                    if(@!mkdir($pathAll, 0777, true)){
                        $hint = 'N';
                    }
                }
                $pathNo = '';
            }
        }
        return $hint;
    }

    /**
     * 数据库备份
     * 参数：备份哪个表(可选),备份目录(可选，默认为backup),分卷大小(可选,默认10Mb)
     *
     * @param string/array $tablename
     * @param string $dir
     * @param int $size
     */
    function backup($tablename = '', $dir='./', $size=10) { //size 兆
        $tablenames = $tablename;
        $dir = $dir ? $dir : './';

        if(!file_exists($dir)){
            $result = $this->pathcreates($dir);
            if($result == 'N'){
                $this->_showMsg('创建文件夹失败','',true);
                exit;
            }
        }
        $size = intval($size) ? intval($size) : 1;
        $size = $size*1024*1024; // Mb
        $sql = '';
        // 只备份单个表
        if(is_string( $tablename ) && !empty($tablename)){
            if(@mysqli_num_rows(mysqli_query($this->db,"SHOW TABLES LIKE '".$tablename."'")) == 1){

            }else{
                $this->_showMsg('表-<b>' . $tablename .'</b>-不存在，请检查！',true);exit();
            }
            $this->_showMsg('<p>正在备份表 <span class="color999">'.$tablename.'</span></p>');
            // 插入页眉
            $sql = $this->_sqlheader();
            // 插入表结构信息
            $sql .= $this->_insert_table_structure($tablename);
            // 插入数据
            $data = mysqli_query($this->db,"select * from `$tablename` ");
            // 文件名前面部分
            $filename = date('Y-m-d-H-i-s' )."_".$tablename;
            // 字段数量
            $num_fields = mysqli_num_fields($data);
            // 第几分卷
            $p = 1;
            // 循环每条记录
            while($record = mysqli_fetch_array($data,MYSQLI_NUM)){
                // 单条记录
                $sql .= $this->_insert_record($tablename, $num_fields, $record);
                // 如果大于分卷大小，则写入文件
                if(strlen($sql) >= $size) {
                    $file = $filename.'_page_'.$p.'.sql';
                    if($this->_write_file($sql, $file, $dir)){
                        $this->_showMsg("表-<b>" . $tablename . "</b>-卷-<b>" . $p . "</b>-数据备份完成，备份文件  <span class='color999'>".$file."</span>");
                    }else{
                        $this->_showMsg("备份表 -<b>" . $tablename . "</b>- 失败",true,true);
                        return false;
                    }
                    // 下一个分卷
                    $p ++;
                    // 重置$sql变量为空，重新计算该变量大小
                    $sql = "";
                }
            }
            // 及时清除数据
            unset($data,$record);
            // sql大小不够分卷大小
            if($sql != ""){
                $filename .= "_page_".$p.".sql";
                if($this->_write_file($sql, $filename, $dir)){
                    $this->_showMsg( "表-<b>" . $tablename . "</b>-卷-<b>" . $p . "</b>-数据备份完成，备份文件 <span class='color999'>".$filename."</span>");
                }else{
                    $this->_showMsg("备份卷-<b>" . $p . "</b>-失败<br />",'',true);
                    return false;
                }
            }
            $this->_showMsg("恭喜您! <span class='imp'>备份成功</span>",'',true);
        }else{
            // 备份多个表
            $this->_showMsg('正在备份……');
            // 读取全部表
            if($tables = mysqli_query($this->db,"show table status from `".$this->database."`")) {
                $this->_showMsg("读取数据库结构成功！");
            }else{
                $this->_showMsg("读取数据库结构失败！",'',true);exit;
            }
            // 插入页眉
            $sql .= $this->_sqlheader ();
            // 文件名前面部分
            $filename = date('Y-m-d-H-i-s')."_all";
            // 查出所有表
            $table = mysqli_query($this->db,'SHOW TABLES');

            // 第几分卷
            $p = 1;
            // 循环所有表
            while($table = mysqli_fetch_array($tables,MYSQLI_NUM)){
                // 获取表名
                $tablename = $table[0];
                //匹配已选择的表
                if(is_array($tablenames) && !empty($tablenames)){
                    if(in_array($tablename,$tablenames)){
                        // 获取表结构
                        $sql .= $this->_insert_table_structure($tablename);
                        $data = mysqli_query($this->db,"select * from `$tablename`");
                        $num_fields = mysqli_num_fields($data);

                        // 循环每条记录
                        while($record = mysqli_fetch_array($data,MYSQLI_NUM)){
                            // 单条记录
                            $sql .= $this->_insert_record($tablename, $num_fields, $record);
                            // 如果大于分卷大小，则写入文件
                            if(strlen($sql) >= $size) {

                                $file = $filename."_page_".$p.".sql";
                                // 写入文件
                                if($this->_write_file($sql, $file, $dir )) {
                                    $this->_showMsg("-卷-<b>" . $p . "</b>-数据备份完成，备份文件 <span class='color999'>".$file."</span>");
                                }else{
                                    $this->_showMsg("卷-<b>" . $p . "</b>-备份失败!",true,true);
                                    return false;
                                }
                                // 下一个分卷
                                $p ++;
                                // 重置$sql变量为空，重新计算该变量大小
                                $sql = "";
                            }
                        }
                    }
                }
            }
            // sql大小不够分卷大小
            if($sql != "") {
                $filename .= "_page_".$p.".sql";
                if ($this->_write_file ($sql, $filename, $dir)){
                    $this->_showMsg("-卷-<b>" . $p . "</b>-数据备份完成，备份文件 <span class='color999'>".$filename."</span>");
                } else {
                    $this->_showMsg("卷-<b>" . $p . "</b>-备份失败",true,true);
                    return false;
                }
            }
            $this->_showMsg("恭喜您! <span class='imp'>备份成功</span>",'',true);
        }
    }

    //  及时输出信息
    private function _showMsg($msg,$err=false,$flag=false){
        echo '<!DOCTYPE html>
			<html>
			  <head>
				<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
				<title>数据库备份还原中……</title>
				<style>
				body{background:#ffffff;padding:30px;}
				p{line-height:20px;font-size:16px;}
				.sub_btn{padding:6px 15px;background:#00BCD4;color:#fff;margin-right:20px;text-decoration:none;}
				.color999{color:#999;}
				</style>
			  </head>
			<body>';
        $err = $err ? '<span>ERROR: </span>' : '' ;
        echo '<p>'.$err.$msg.'</p>';
        if($flag == true){
            echo '<p><a href="javascript:self.location=document.referrer;" class="sub_btn">返回</a>';
        }
        echo '</body></html>';

        flush();

    }

    /**
     * sql 文件页眉
     * @return string
     */
    private function _sqlheader() {
        $value = '';
        $value .= '-- -------------------------------------------------------'.$this->ds;
        $value .= $this->ds;
        $value .= '-- MySQL database dump'.$this->ds;
        $value .= '-- Created by ManageDB class'.$this->ds;
        $value .= $this->ds;
        $value .= '-- 主机: '.$this->host.$this->ds;
        $value .= '-- 生成日期: '.date('Y年m月d日 H:i:s').$this->ds;
        $value .= '-- MySQL版本:'.mysqli_get_server_info($this->db).$this->ds;
        $value .= '-- PHP 版本: '.phpversion (). $this->ds;
        $value .= $this->ds;
        $value .= '-- 数据库: `'.$this->database.'`'.$this->ds;
        $value .= $this->ds;
        $value .= '-- -------------------------------------------------------'.$this->ds;
        $value .= '-- 高版本兼容低版本'.$this->ds;
        $value .= "show session variables like '%sql_mode%';".$this->ds;
        $value .= 'set sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";'.$this->ds;
        $value .= '-- -------------------------------------------------------'.$this->ds;
        $value .= $this->ds;
        $value .= $this->ds;
        $value .= $this->ds;
        return $value;
    }

    /**
     * 插入表结构
     * @return string
     */
    private function _insert_table_structure($table) {
        $sql = $this->ds;
        $sql .= "--".$this->ds;
        $sql .= "-- 表的结构".$table.$this->ds;
        $sql .= "--".$this->ds.$this->ds;

        // 如果存在则删除表
        $sql .= "DROP TABLE IF EXISTS `".$table.'`'.$this->sqlEnd.$this->ds;
        // 获取详细表信息
        $res = mysqli_query($this->db,'SHOW CREATE TABLE `'.$table.'`');
        $row = mysqli_fetch_array($res,MYSQLI_NUM);
        $sql .= $row[1];
        $sql .= $this->sqlEnd.$this->ds;
        // 加上
        $sql .= $this->ds;
        $sql .= "--".$this->ds;
        $sql .= "-- 转存表中的数据 ".$table.$this->ds;
        $sql .= "--".$this->ds;
        $sql .= $this->ds;
        return $sql;
    }

    /**
     * 插入单条记录
     *
     * @param string $table
     * @param int $num_fields
     * @param array $record
     * @return string
     */
    private function _insert_record($table, $num_fields, $record) {
        // sql字段逗号分割
        $insert = '';
        $comma = "";
        $insert .= "INSERT INTO `".$table."` VALUES(";
        // 循环每个子段下面的内容
        for($i = 0; $i < $num_fields; $i ++) {
            $v = (string)$record[$i];
            $str = htmlspecialchars($v);
            $str = htmlspecialchars_decode($str);
            $str = str_replace(array("\r\n", "\r", "\n", PHP_EOL), '\r\n', $str);
            $insert .= ($comma . "'" . $str . "'");
            $comma = ",";
        }
        $insert .= ");".$this->ds;
        return $insert;
    }

    /**
     * 写入文件
     *
     * @param string $sql
     * @param string $filename
     * @param string $dir
     * @return boolean
     */
    private function _write_file($sql, $filename, $dir) {
        $dir = $dir ? $dir : SYS_DB_PATH;
        // 创建目录
        if (! is_dir ( $dir )) {
            mkdir ($dir, 0777, true);
        }
        $re = true;
        if(! @$fp = fopen($dir.$filename, "w+")) {
            $re = false;
            $this->_showMsg("打开sql文件失败！",true,true);
        }
        if (!@fwrite($fp, $sql)) {
            $re = false;
            $this->_showMsg("写入sql文件失败，请文件是否可写",true,true);
        }
        if (!@fclose($fp)) {
            $re = false;
            $this->_showMsg("关闭sql文件失败！",true,true);
        }
        return $re;
    }

    /**
     * 导入备份数据
     * 说明：分卷文件格式20181101211738_all_page_1.sql
     * 参数：文件路径(必填)
     *
     * @param string $sqlfile
     */
    function restore($sqlfile) {
        // 检测文件是否存在
        if(!file_exists($sqlfile)){
            $this->_showMsg("sql文件不存在！请检查",true);
            exit();
        }
        $this->lock($this->database);
        // 获取数据库存储位置
        $sqlpath = pathinfo($sqlfile);
        $this->sqldir = $sqlpath['dirname'];
        // 检测是否包含分卷，从_page分开,有则说明有分卷
        $volume = explode ( "_page_", $sqlfile );
        $volume_path = $volume[0];
        $this->_showMsg("请勿刷新及关闭浏览器以防止程序被中止，如有不慎！将导致数据库结构受损");
        $this->_showMsg("正在导入备份数据，请稍等！");
        if(empty($volume[1])){
            $this->_showMsg( "正在导入sql：<span class='imp'>".$sqlfile.'</span>');
            // 没有分卷
            if($this->_import($sqlfile)){
                $this->_showMsg("数据库导入成功！",'',true);

            }else{
                $this->_showMsg('数据库导入失败！','',true);
                exit();
            }
        }else{
            // 存在分卷，则获取当前是第几分卷，循环执行余下分卷
            $volume_id = explode(".sq", $volume[1]);
            // 当前分卷为$volume_id
            $volume_id = intval($volume_id[0]);
            while($volume_id){
                $tmpfile = $volume_path."_page_".$volume_id.".sql";
                // 存在其他分卷，继续执行
                if(file_exists($tmpfile)){
                    // 执行导入方法
                    $this->msg = "正在导入分卷 $volume_id ：<span style='color:#f00;'>" . $tmpfile . '</span><br />';
                    if($this->_import($tmpfile)){
                        $this->_showMsg($this->msg);
                    }else{
                        $volume_id = $volume_id ? $volume_id :1;
                        $this->_showMsg( "导入分卷：<span style='color:#f00;'>" . $tmpfile . '</span>失败！可能是数据库结构已损坏！请尝试从分卷1开始导入','',true );
                        exit;
                    }
                }else{
                    $this->msg = "此分卷备份全部导入成功！<br />";
                    $this->_showMsg($this->msg,'',true);
                    return;
                }
                $volume_id ++;
            }
        }
    }

    /**
     * 将sql导入到数据库（普通导入）
     *
     * @param string $sqlfile
     * @return boolean
     */
    private function _import($sqlfile){
        // sql文件包含的sql语句数组
        $sqls = array();
        $f = fopen($sqlfile, "rb");
        // 创建表缓冲变量
        $create_table = '';
        while(!feof($f)){
            // 读取每一行sql
            $line = fgets ($f);
            // 这一步为了将创建表合成完整的sql语句
            // 如果结尾没有包含';'(即为一个完整的sql语句，这里是插入语句)，并且不包含'ENGINE='(即创建表的最后一句)
            if (! preg_match ( '/;/', $line ) || preg_match ( '/ENGINE=/', $line)){
                // 将本次sql语句与创建表sql连接存起来
                $create_table .= $line;
                // 如果包含了创建表的最后一句
                if (preg_match ( '/ENGINE=/', $create_table)){
                    //执行sql语句创建表
                    $this->_insert_into($create_table);
                    // 清空当前，准备下一个表的创建
                    $create_table = '';
                }
                // 跳过本次
                continue;
            }
            //执行sql语句
            $this->_insert_into($line);
        }
        fclose ($f);
        return true;
    }

    //插入单条sql语句
    private function _insert_into($sql){
        if (!mysqli_query($this->db,trim($sql))){
            $this->msg .= mysqli_error($this->db);
            return false;
        }
    }

    // 关闭数据库连接
    private function close() {
        mysqli_close ($this->db);
    }

    // 锁定数据库，以免备份或导入时出错
    private function lock($tablename, $op = "WRITE"){
        if (mysqli_query($this->db,"lock tables `".$tablename."` ".$op )) {
            return true;
        }else {
            return false;
        }
    }

    // 解锁
    private function unlock(){
        if (mysqli_query($this->db,"unlock tables")) {
            return true;
        }else {
            return false;
        }
    }

    // 析构
    function __destruct(){
        if($this->db){
            mysqli_query($this->db,"unlock tables");
            mysqli_close($this->db);
        }
    }

}
